package com.flymobi.util;

public class QL {
	public static final class HQL {

		public static final class Sp {
			public static final String FIND_BY_Name = "from Sp m WHERE m.loginName=?";
			public static final String FIND_BY_SPNUMBER = "from Sp m WHERE m.spNumber=? and m.subSpNumber=?";
			public static final String LIST= "from Sp m order by m.id desc";
			public static final String GET_COUNT= "SELECT COUNT(*) FROM Sp ";
		}

		public static class Manager {
			public static final String LIST = "from Manager m ORDER BY m.id DESC";
			public static final String FIND_BY_NAME = "from Manager m where m.name=?";
			public static final String GET_COUNT = "SELECT COUNT(*) FROM Manager m";
			public static final String LIST_KEY = "from Manager m where m.name like ? ORDER BY m.id DESC";
			public static final String GET_COUNT_KEY = "SELECT COUNT(*) FROM Manager m where m.name like ?";
		}

		public static final class SysPower {
			public static final String LIST = "FROM SysPower m WHERE m.rolesId=?";
			public static final String DELETE_BY_MENUID = "DELETE FROM SysPower m WHERE m.sysMenuId=?";
			public static final String DELETE_BY_RolesID = "DELETE FROM SysPower m WHERE m.rolesId=?";
		}

		public static final class SysMenu {
			public static final String LIST = "FROM SysMenu m WHERE m.fatherId=? order by m.idx";
			public static String LIST_BY_POWER = "FROM SysMenu m WHERE m.fatherId=? and m.id in(SELECT s.sysMenuId FROM SysPower s WHERE s.rolesId=?) order by m.idx";
		}

		public static final class Roles {

			public static final String LIST = "from Roles m ORDER BY m.id DESC";

		}

		public static final class SmsLog{

			public static final String LIST = null;
			public static final String GET_COUNT = null;
			
		}

		public static final class SmsTask {
			
			public static final String GET_TASK = "from SmsTask m where m.shenhe=1 and m.status=0 order by m.id";
			public static final String GET_SendingTASK = "from SmsTask m where m.shenhe=1 and m.status=1 order by m.id";
			
		}
	}

	public static final class SQL {

		public static final class MYSQL {
			public static final class PAGINATION {
				public static String SQL(String sql, int startRows,
						int pagePerSize) {
					return "select * from (" + sql + ") ttt order by c1 desc limit " + startRows
							+ "," + pagePerSize;
				}
			}

			public static final class TONGJI {
				
				
				public static final String TONGJI_BY_DAY = "select a.time c1,a.pv c2,c.uv c3,b.ipcount c4 from "
						+ " (select v.day time,count(*) pv from ViewLog v where v.day=?) a "
						+ " left join (select v.day time,count(distinct v.ip) ipcount from ViewLog v where v.day=?) b on b.time=a.time "
						+ " left join (select v.day time,count(distinct v.mobile) uv from ViewLog v where v.day=?) c on c.time=a.time ";

				public static final String getcountbyday = "select count(*) from (select v.day time from ViewLog v where v.savetime>=? and v.savetime<? group by v.day)) m"; 

				public static final String TONGJI_BY_PINDAO = "select v.name,ifnull(a.c,0) from Pindao v left join (select v.pindaoId,count(*) c from ViewLog v where v.day=DATE_FORMAT(?,'%m%d') and v.pindaoId>0 group by v.pindaoId) a on a.pindaoId=v.id where v.fatherId=0 and v.id!=79 order by a.c desc";
				
				public static final String TONGJI_BY_Version = "select ifnull(v.v,'δ֪'),count(*) c from ViewLog v where v.day=DATE_FORMAT(?,'%m%d') group by v.v order by c desc";
				
				public static final String TONGJI_PAGE = "select v.actionid,count(*) pv from ViewLog v where v.pindaoId=? and v.savetime>=? and v.savetime<? and v.actionid!=3 group by v.actionid";
				
				public static final String TONGJI_PAGE2 = "select v.name,ifnull(a.pv,0) from Pindao v left join (select v.lanmuId,count(*) pv from ViewLog v where v.pindaoId=? and v.savetime>=? and v.savetime<? and v.actionid=3 group by v.lanmuId) a on a.lanmuId=v.id where v.fatherId=?";

				public static final String TONGJI_Area = "select v.province,count(*) pv from ViewLog v where v.savetime>=? and v.savetime<? and v.province is not null group by v.province order by pv desc";

				public static final String TONGJI_Qudao = "select v.name,ifnull(a.pv,0) from Qudao v left join (select v.qudaoId,count(*) pv from ViewLog v where v.savetime>=? and v.savetime<? group by v.qudaoId) a on a.qudaoId=v.id order by a.pv desc";

				public static final String TONGJI_Ad = "select ifnull(v.type1,'0'),ifnull(v.newsId,0),count(*) c from ViewLog v where v.savetime>=? and v.savetime<? and actionid=18 group by type1,newsId order by c desc";

				public static final String TONGJI_BY_HOUR ="select a.time c1,a.pv c2,c.uv c3,b.ipcount c4 from "
					+ " (select v.hour time,count(*) pv from ViewLog v where v.day=DATE_FORMAT(?,'%m%d') group by v.hour) a "
					+ " left join (select v.hour time,count(distinct v.ip) ipcount from ViewLog v where v.day=DATE_FORMAT(?,'%m%d') group by v.hour) b on b.time=a.time "
					+ " left join (select v.hour time,count(distinct v.mobile) uv from ViewLog v where v.day=DATE_FORMAT(?,'%m%d') group by v.hour) c on c.time=a.time order by a.time";
			}  
		}
	}
}